﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Business;
using EntitiesTM;
using System.Threading;
using System.Data;
using System.Text;
using System.IO;

namespace TimeWork.webmaster.report
{
    public partial class TotalMonth : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (Session["mail"] == null)
            {
                Response.Redirect("http://" + Request.Url.Authority + "/webmaster/SignIn.aspx");
            }
            if (!IsPostBack)
            {
                loadContent();
                rbMonth.Checked = true;
                showControl(true);
                BindDdlProject();
                ddlActivity.Visible = false;
            }
        }

        protected void ddlCompany_SelectedIndexChanged(object sender, EventArgs e)
        {
            Session["companyid"] = ddlCompany.SelectedValue;
        }

        private void loadContent()
        {
            ddlCompany.DataSource = BuCompany.selAllCompany();
            ddlCompany.DataTextField = "CompanyName";
            ddlCompany.DataValueField = "CompanyID";
            ddlCompany.DataBind();
            if (Session["companyid"] == null)
            {
                Session["companyid"] = "1";
            }

            ddlCompany.SelectedValue = Session["companyid"].ToString();

        }


        protected void rbMonth_CheckedChanged(object sender, EventArgs e)
        {
            showControl(true);
        }

        protected void rbYear_CheckedChanged(object sender, EventArgs e)
        {
            showControl(false);
        }

        private void showControl(bool cond)
        {
            if (cond)
            {
                ddlMonth.Visible = true;
                ddlYear.Visible = true;
                ddlTimeYear.Visible = false;
            }
            else
            {
                ddlMonth.Visible = false;
                ddlYear.Visible = false;
                ddlTimeYear.Visible = true;
            }
        }

        private void BindDdlProject()
        {
            Dictionary<string, string> data = BuProject.selProjectByParentIDTreeReport(0, Session["companyid"].ToString());
            ListItem item;
            item = new ListItem("Tất cả sản phẩm", "0");
            ddlproject.Items.Add(item);

            foreach (KeyValuePair<string, string> pair in data)
            {
                item = new ListItem(pair.Value, pair.Key);
                ddlproject.Items.Add(item);
            }
        }

        protected void ddlproject_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (!ddlproject.SelectedValue.Equals("0"))
            {
                int projectLevel = BuProject.selProjectByIDTree(Int32.Parse(ddlproject.SelectedValue), Session["companyid"].ToString()).projectLevel;
                if (projectLevel == 3)
                {
                    // Cho hien DropDownList activity va do du lieu
                    ddlActivity.Visible = true;
                    EnProject project = new EnProject();
                    project.projectID = Int32.Parse(ddlproject.SelectedValue);
                    ddlActivity.DataSource = BuProject.selActivityByProjectExceptID(project, Session["companyid"].ToString());
                    ddlActivity.DataTextField = "ActivityName";
                    ddlActivity.DataValueField = "ActivityID";
                    ddlActivity.DataBind();
                    ddlActivity.Items.Insert(0, new ListItem("Tất cả công việc", "0"));
                    ddlActivity.SelectedValue = "0";
                }
                else
                {
                    ddlActivity.Visible = false;
                }
                string value = ddlActivity.SelectedValue;
            }
            else
            {
                ddlActivity.Visible = false;
            }
        }

        private string genSqlStringUnit()
        {
            string companyid = Session["companyid"].ToString();
            string result = String.Empty;
            try
            {
                result += "SELECT r.ResultUnitID, r.ResultUnitName, r.ResultUnitNameAlt, r.ResultUnitOrder, ISNULL(SUM(SAReportTimeHour),0) TimeHour, ISNULL(SUM(SAReportTimeMinute),0) TimeMinute,";
                result += "ISNULL(SUM(CASE WHEN SATypeTimeID = 1 THEN SAReportTimeHour END),0) FirstTimeH,";
                result += "ISNULL(SUM(CASE WHEN SATypeTimeID = 1 THEN SAReportTimeMinute END),0) FirstTimeM,";
                result += "ISNULL(SUM(CASE WHEN SATypeTimeID = 2 THEN SAReportTimeHour END),0) SecondTimeH,";
                result += "ISNULL(SUM(CASE WHEN SATypeTimeID = 2 THEN SAReportTimeMinute END),0) SecondTimeM,";
                result += "ISNULL(SUM(CASE WHEN SATypeTimeID = 3 THEN SAReportTimeHour END),0) ThirdTimeH,";
                result += "ISNULL(SUM(CASE WHEN SATypeTimeID = 3 THEN SAReportTimeMinute END),0) ThirdTimeM ";
                result += "FROM VTK_ResultUnit" + companyid + " as r FULL JOIN VTK_Staff" + companyid + " as s ON r.ResultUnitID = s.StaffResultUnitID ";
                result += "FULL JOIN (SELECT SAStaffID,SADate, SAReportTimeHour, SAReportTimeMinute, SATypeTimeID, SAActivityID FROM VTK_SA" + companyid + " WHERE ";
                // Neu bao cao theo thang
                if (rbMonth.Checked)
                {
                    // Lay thang va nam tu dropdownlist
                    int month = Int32.Parse(ddlMonth.SelectedValue);
                    int year = Int32.Parse(ddlYear.SelectedValue);

                    result += "DATEPART(M,SADate) = " + month + " AND DATEPART(YY,SADate) = " + year + " ";
                }
                // Bao cao theo nam
                else
                {
                    int timeYear = Int32.Parse(ddlTimeYear.SelectedValue);
                    result += "DATEPART(YY,SADate) = " + timeYear + " ";
                }
                // Lay id cua project
                int projectID = Int32.Parse(ddlproject.SelectedValue);
                // Neu projectID khac 0, truy van toan bo bang theo datetime

                if (projectID != 0)
                {
                    // Neu project != 0 thi phai them dieu kien
                    int projectLevel = BuProject.selProjectByIDTree(Int32.Parse(ddlproject.SelectedValue), Session["companyid"].ToString()).projectLevel;
                    // Truong hop level = 3 thi xuat hien lua chon cong viec
                    if (projectLevel == 3)
                    {
                        // Truong hop chon tat ca
                        if (ddlActivity.SelectedValue.Equals("0"))
                        {
                            result += "AND SAActivityID IN (SELECT ActivityID FROM VTK_Activity" + companyid + " WHERE ActivityProjectID = " + projectID + ")";
                        }
                        // Neu chi chon lua 01 activity
                        else
                        {
                            result += "AND SAActivityID = " + ddlActivity.SelectedValue;
                        }
                    }
                    if (projectLevel == 2)
                    {
                        result += "AND SAActivityID IN (SELECT ActivityID FROM VTK_Activity" + companyid + " WHERE ActivityProjectID IN (SELECT ProjectID FROM VTK_Project" + companyid + " WHERE ProjectParentID = " + projectID + "))";
                    }
                    if (projectLevel == 1)
                    {
                        result += "AND SAActivityID IN (SELECT ActivityID FROM VTK_Activity" + companyid + " WHERE ActivityProjectID IN (SELECT ProjectID FROM VTK_Project" + companyid + " WHERE ProjectParentID IN (SELECT ProjectID FROM VTK_Project" + companyid + " WHERE ProjectParentID = " + projectID + ")))";
                    }
                }

                result += ") as sa ON sa.SAStaffID = s.StaffID FULL JOIN VTK_TypeTime" + companyid + " as t ON sa.SATypeTimeID = t.TypeTimeID ";
                result += "GROUP BY r.ResultUnitID, r.ResultUnitName, r.ResultUnitNameAlt, r.ResultUnitOrder ORDER BY ResultUnitOrder";
            }
            catch { }
            return result;
        }

        private string genSqlStringUser(string resultUnitID)
        {
            string companyid = Session["companyid"].ToString();
            string result = String.Empty;
            try
            {
                result += "SELECT s.StaffID,s.StaffName, s.StaffScale, s.StaffLevel, ISNULL(SUM(SAReportTimeHour),0) TimeHour, ISNULL(SUM(SAReportTimeMinute),0) TimeMinute,";
                result += "ISNULL(SUM(CASE WHEN SATypeTimeID = 1 THEN SAReportTimeHour END),0) FirstTimeH,";
                result += "ISNULL(SUM(CASE WHEN SATypeTimeID = 1 THEN SAReportTimeMinute END),0) FirstTimeM,";
                result += "ISNULL(SUM(CASE WHEN SATypeTimeID = 2 THEN SAReportTimeHour END),0) SecondTimeH,";
                result += "ISNULL(SUM(CASE WHEN SATypeTimeID = 2 THEN SAReportTimeMinute END),0) SecondTimeM,";
                result += "ISNULL(SUM(CASE WHEN SATypeTimeID = 3 THEN SAReportTimeHour END),0) ThirdTimeH,";
                result += "ISNULL(SUM(CASE WHEN SATypeTimeID = 3 THEN SAReportTimeMinute END),0) ThirdTimeM ";
                result += "FROM VTK_ResultUnit" + companyid + " as r FULL JOIN VTK_Staff" + companyid + " as s ON r.ResultUnitID = s.StaffResultUnitID ";
                result += "FULL JOIN (SELECT SAStaffID,SADate, SAReportTimeHour, SAReportTimeMinute, SATypeTimeID, SAActivityID FROM VTK_SA" + companyid + " WHERE ";
                // Neu bao cao theo thang
                if (rbMonth.Checked)
                {
                    // Lay thang va nam tu dropdownlist
                    int month = Int32.Parse(ddlMonth.SelectedValue);
                    int year = Int32.Parse(ddlYear.SelectedValue);

                    result += "DATEPART(M,SADate) = " + month + " AND DATEPART(YY,SADate) = " + year + " ";
                }
                // Bao cao theo nam
                else
                {
                    int timeYear = Int32.Parse(ddlTimeYear.SelectedValue);
                    result += "DATEPART(YY,SADate) = " + timeYear + " ";
                }
                // Lay id cua project
                int projectID = Int32.Parse(ddlproject.SelectedValue);
                // Neu projectID khac 0, truy van toan bo bang theo datetime

                if (projectID != 0)
                {
                    // Neu project != 0 thi phai them dieu kien
                    int projectLevel = BuProject.selProjectByIDTree(Int32.Parse(ddlproject.SelectedValue), Session["companyid"].ToString()).projectLevel;
                    // Truong hop level = 3 thi xuat hien lua chon cong viec
                    if (projectLevel == 3)
                    {
                        // Truong hop chon tat ca
                        if (ddlActivity.SelectedValue.Equals("0"))
                        {
                            result += "AND SAActivityID IN (SELECT ActivityID FROM VTK_Activity" + companyid + " WHERE ActivityProjectID = " + projectID + ")";
                        }
                        // Neu chi chon lua 01 activity
                        else
                        {
                            result += "AND SAActivityID = " + ddlActivity.SelectedValue;
                        }
                    }
                    if (projectLevel == 2)
                    {
                        result += "AND SAActivityID IN (SELECT ActivityID FROM VTK_Activity" + companyid + " WHERE ActivityProjectID IN (SELECT ProjectID FROM VTK_Project" + companyid + " WHERE ProjectParentID = " + projectID + "))";
                    }
                    if (projectLevel == 1)
                    {
                        result += "AND SAActivityID IN (SELECT ActivityID FROM VTK_Activity" + companyid + " WHERE ActivityProjectID IN (SELECT ProjectID FROM VTK_Project" + companyid + " WHERE ProjectParentID IN (SELECT ProjectID FROM VTK_Project" + companyid + " WHERE ProjectParentID = " + projectID + ")))";
                    }
                }

                result += ") as sa ON sa.SAStaffID = s.StaffID FULL JOIN VTK_TypeTime" + companyid + " as t ON sa.SATypeTimeID = t.TypeTimeID ";
                result += "WHERE s.StaffResultUnitID = " + resultUnitID + " ";
                result += "GROUP BY s.StaffID,s.StaffName, s.StaffScale, s.StaffLevel ";
                result += "ORDER BY s.StaffName";

            }
            catch { }
            return result;
        }

        protected void btnGenerate_Click(object sender, EventArgs e)
        {
            ltrData.Text = String.Empty;
            Thread.Sleep(500);
            string label = rbMonth.Checked ? "tháng" : "năm";
            DataTable dataUnit = BuReport.selReportDetailedOri(genSqlStringUnit());
            string tmp = @"<table cellspacing='0'><thead><tr>
			               <th rowspan='2' style='text-align:center;color:#375B91;background-color:#F3F9FF;width:29px;border: 1px solid #CCC;'>TT</th>
                           <th rowspan='2' style='text-align:center;color:#375B91;background-color:#F3F9FF;width:240px;border: 1px solid #CCC;'>Đơn vị/Cá nhân</th>
                           <th rowspan='2' style='text-align:center;color:#375B91;background-color:#F3F9FF;width:60px;border: 1px solid #CCC;'>Mã đơn vị/Công chức</th>
                           <th rowspan='2' style='text-align:center;color:#375B91;background-color:#F3F9FF;width:80px;border: 1px solid #CCC;'>Thời gian sử dụng trong " + label + "</th>";
            tmp += @"<th colspan='3' style='text-align:center;color:#375B91;background-color:#F3F9FF;border: 1px solid #CCC;'>Phân loại theo thời gian</th>
                           </tr>
                        <tr>
                           <th style='text-align:center;color:#375B91;background-color:#F3F9FF;width:110px;border: 1px solid #CCC;'>Giờ tiêu chuẩn</th>
                           <th style='text-align:center;color:#375B91;background-color:#F3F9FF;width:120px;border: 1px solid #CCC;'>Ngoài giờ</th>
                           <th style='text-align:center;color:#375B91;background-color:#F3F9FF;width:90px;border: 1px solid #CCC;'>Ngày nghỉ(cuối tuần, lễ, tết)</th>
		                </tr></thead><tbody>";

            int hour, minute, addHour;
            // Bien luu tru tong tam thoi
            int sumH = 0, sumM = 0, sumH1 = 0, sumM1 = 0, sumH2 = 0, sumM2 = 0, sumH3 = 0, sumM3 = 0;
            int count = 1;
            string dataString = String.Empty;
            foreach (DataRow item in dataUnit.Rows)
            {
                if (!item["ResultUnitID"].ToString().Equals(""))
                {
                    // Thoi gian su dung
                    hour = Int32.Parse(item["TimeHour"].ToString());
                    minute = Int32.Parse(item["TimeMinute"].ToString());
                    sumH += hour;
                    sumM += minute;

                    if (minute >= 60)
                    {
                        addHour = minute / 60;
                        minute -= 60 * addHour;
                        hour += addHour;
                    }
                    dataString += "<tr><td style='text-align:center;border: 1px solid #CCC;'>" + count + "</td><td style='border: 1px solid #CCC;'>" + item["ResultUnitName"].ToString() + "</td><td style='text-align:center;border: 1px solid #CCC;'>" + item["ResultUnitNameAlt"].ToString() + "</td><td style='text-align:center;border: 1px solid #CCC;'>" + hour + "," + minute + "</td>";

                    // Gio tieu chuan
                    hour = Int32.Parse(item["FirstTimeH"].ToString());
                    minute = Int32.Parse(item["FirstTimeM"].ToString());
                    sumH1 += hour;
                    sumM1 += minute;
                    if (minute >= 60)
                    {
                        addHour = minute / 60;
                        minute -= 60 * addHour;
                        hour += addHour;
                    }
                    dataString += "<td style='text-align:center;border: 1px solid #CCC;'>" + hour + "," + minute + "</td>";
                    // Ngoai gio
                    hour = Int32.Parse(item["SecondTimeH"].ToString());
                    minute = Int32.Parse(item["SecondTimeM"].ToString());
                    sumH2 += hour;
                    sumM2 += minute;
                    if (minute >= 60)
                    {
                        addHour = minute / 60;
                        minute -= 60 * addHour;
                        hour += addHour;
                    }
                    dataString += "<td style='text-align:center;border: 1px solid #CCC;'>" + hour + "," + minute + "</td>";
                    // Ngay nghi
                    hour = Int32.Parse(item["ThirdTimeH"].ToString());
                    minute = Int32.Parse(item["ThirdTimeM"].ToString());
                    sumH3 += hour;
                    sumM3 += minute;
                    if (minute >= 60)
                    {
                        addHour = minute / 60;
                        minute -= 60 * addHour;
                        hour += addHour;
                    }
                    dataString += "<td style='text-align:center;border: 1px solid #CCC;'>" + hour + "," + minute + "</td></tr>";
                    count++;
                }
            }

            tmp += "<tr><td style='border: 1px solid #CCC;'>&nbsp</td><td style='border: 1px solid #CCC;'><center><strong>Tổng số</strong></center></td><td style='border: 1px solid #CCC;'>&nbsp</td>";

            // Thoi gian su dung
            if (sumM >= 60)
            {
                addHour = sumM / 60;
                sumM -= 60 * addHour;
                sumH += addHour;
            }
            tmp += "<td style='text-align:center;border: 1px solid #CCC;'>" + sumH + "," + sumM + "</td>";
            // Gio tieu chuan
            if (sumM1 >= 60)
            {
                addHour = sumM1 / 60;
                sumM1 -= 60 * addHour;
                sumH1 += addHour;
            }
            tmp += "<td style='text-align:center;border: 1px solid #CCC;'>" + sumH1 + "," + sumM1 + "</td>";
            // Ngoai gio
            if (sumM2 >= 60)
            {
                addHour = sumM2 / 60;
                sumM2 -= 60 * addHour;
                sumH2 += addHour;
            }
            tmp += "<td style='text-align:center;border: 1px solid #CCC;'>" + sumH2 + "," + sumM2 + "</td>";
            // Ngay nghi
            if (sumM3 >= 60)
            {
                addHour = sumM3 / 60;
                sumM3 -= 60 * addHour;
                sumH3 += addHour;
            }
            tmp += "<td style='text-align:center;border: 1px solid #CCC;'>" + sumH3 + "," + sumM3 + "</td></tr>";
            tmp += "<tr><td style='text-align:center;border: 1px solid #CCC;background-color:#FFC;'><strong>I</strong></td><td style='border: 1px solid #CCC;background-color:#FFC;'><strong>Theo đơn vị</strong></td><td style='border: 1px solid #CCC;background-color:#FFC;'>&nbsp</td><td style='border: 1px solid #CCC;background-color:#FFC;'>&nbsp</td><td style='border: 1px solid #CCC;background-color:#FFC;'>&nbsp</td><td style='border: 1px solid #CCC;background-color:#FFC;'>&nbsp</td><td style='border: 1px solid #CCC;background-color:#FFC;'>&nbsp</td></tr>";
            tmp += dataString;

            // Bao cao theo ca nhan
            tmp += "<tr><td style='text-align:center;border: 1px solid #CCC;background-color:#FFC;'><strong>II</strong></td><td style='border: 1px solid #CCC;background-color:#FFC;'><strong>Theo cá nhân</strong></td><td style='background-color:#FFC;border: 1px solid #CCC;'>&nbsp</td><td style='background-color:#FFC;border: 1px solid #CCC;'>&nbsp</td><td style='background-color:#FFC;border: 1px solid #CCC;'>&nbsp</td><td style='background-color:#FFC;border: 1px solid #CCC;'>&nbsp</td><td style='background-color:#FFC;border: 1px solid #CCC;'>&nbsp</td></tr>";


            DataTable dataUser;
            foreach (DataRow item in dataUnit.Rows)
            {
                count = 1;
                if (!item["ResultUnitID"].ToString().Equals(""))
                {
                    tmp += "<tr><td style='background-color:#F2F2F2;text-align:center;border: 1px solid #CCC;'>&nbsp;</td><td style='background-color:#F2F2F2;border: 1px solid #CCC;'><strong><i>" + item["ResultUnitName"].ToString() + "</i></strong></td><td style='background-color:#F2F2F2;border: 1px solid #CCC;'>&nbsp</td><td style='background-color:#F2F2F2;border: 1px solid #CCC;'>&nbsp</td><td style='background-color:#F2F2F2;border: 1px solid #CCC;'>&nbsp</td><td style='background-color:#F2F2F2;border: 1px solid #CCC;'>&nbsp</td><td style='background-color:#F2F2F2;border: 1px solid #CCC;'>&nbsp</td></tr>";
                    dataUser = BuReport.selReportDetailedOri(genSqlStringUser(item["ResultUnitID"].ToString()));
                    foreach (DataRow user in dataUser.Rows)
                    {
                        // Thoi gian su dung
                        hour = Int32.Parse(user["TimeHour"].ToString());
                        minute = Int32.Parse(user["TimeMinute"].ToString());

                        if (minute >= 60)
                        {
                            addHour = minute / 60;
                            minute -= 60 * addHour;
                            hour += addHour;
                        }
                        tmp += "<tr><td style='text-align:center;border: 1px solid #CCC;'>" + count + "</td><td style='border: 1px solid #CCC;'>" + user["StaffName"].ToString() + "</td><td style='text-align:center;border: 1px solid #CCC;'>" + user["StaffScale"].ToString() + "</td><td style='text-align:center;border: 1px solid #CCC;'>" + hour + "," + minute + "</td>";

                        // Gio tieu chuan
                        hour = Int32.Parse(user["FirstTimeH"].ToString());
                        minute = Int32.Parse(user["FirstTimeM"].ToString());

                        if (minute >= 60)
                        {
                            addHour = minute / 60;
                            minute -= 60 * addHour;
                            hour += addHour;
                        }
                        tmp += "<td style='text-align:center;border: 1px solid #CCC;'>" + hour + "," + minute + "</td>";
                        // Ngoai gio
                        hour = Int32.Parse(user["SecondTimeH"].ToString());
                        minute = Int32.Parse(user["SecondTimeM"].ToString());

                        if (minute >= 60)
                        {
                            addHour = minute / 60;
                            minute -= 60 * addHour;
                            hour += addHour;
                        }
                        tmp += "<td style='text-align:center;border: 1px solid #CCC;'>" + hour + "," + minute + "</td>";
                        // Ngay nghi
                        hour = Int32.Parse(user["ThirdTimeH"].ToString());
                        minute = Int32.Parse(user["ThirdTimeM"].ToString());

                        if (minute >= 60)
                        {
                            addHour = minute / 60;
                            minute -= 60 * addHour;
                            hour += addHour;
                        }
                        tmp += "<td style='text-align:center;border: 1px solid #CCC;'>" + hour + "," + minute + "</td></tr>";
                        count++;
                    }
                }
            }
            tmp += "</tbody></table>";
            ltrData.Text = tmp;

        }

        public override void VerifyRenderingInServerForm(Control control)
        {
            return;
        }
        protected void btnExportExcel_Click(object sender, ImageClickEventArgs e)
        {
            Response.Clear();
            Response.Buffer = true;
            Response.AppendHeader("Content-Disposition", "attachment;filename=BaoCaoThoiGianSD" + DateTime.
            Now.ToString("yyyyMMdd") + ".xls");
            Response.ContentEncoding = System.Text.Encoding.UTF8;
            Response.ContentType = "application/ms-excel";
            this.EnableViewState = false;

            var sb = new StringBuilder();
            ltrData.RenderControl(new HtmlTextWriter(new StringWriter(sb)));
            Response.Write(sb.ToString());
            Response.End();
        }
    }
}